![BigQueryでGAになったCONTAINS_SUBSTRで正規化検索を試す(全半角カタカナ・記号)](https://devio2023-media.developers.io/wp-content/uploads/2020/09/gcp-eyecatch-bigquery_1200x630.png)
BigQueryでGAになったCONTAINS_SUBSTRで正規化検索を試す(全半角カタカナ・記号)
データアナリティクス事業本部、池田です。
BigQueryの2021/07/19のリリースでCONTAINS_SUBSTRという関数が
generally available(GA) になったので、
動かしてみました。
【 Release notes 】
【 CONTAINS_SUBSTR 】 ※執筆時点で日本語未対応
どんな関数か?
以下は先述の公式ドキュメントの例そのままなのですが、 第一引数の中に、第二引数の値(検索値)が含まれるかをtrue/falseで評価してくれます。
SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result; +--------+ | result | +--------+ | true | +--------+
その際には、大文字/小文字は区別しません。
便利そうなポイントとして、第一引数にテーブルやカラムを指定することができます。
↓テーブルを検索する場合
WITH Recipes AS (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham') SELECT * FROM Recipes WHERE CONTAINS_SUBSTR(Recipes, 'toast'); +-------------------+-------------------------+------------------+ | Breakfast | Lunch | Dinner | +-------------------+-------------------------+------------------+ | Potato pancakes | Toasted cheese sandwich | Beef stroganoff | | Avocado toast | Tomato soup | Blueberry samon | +-------------------+-------------------------+------------------+
(献立(テーブル)の中から、どこかにトーストを含む1日(レコード)を抽出するイメージですかね。)
↓カラムを指定して検索する場合
WITH Recipes AS (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham') SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch, Dinner), 'potato'); +-------------------+-------------------------+------------------+ | Breakfast | Lunch | Dinner | +-------------------+-------------------------+------------------+ | Bluberry pancakes | Egg salad sandwich | Potato dumplings | | Corned beef hash | Lentil potato soup | Glazed ham | +-------------------+-------------------------+------------------+
(こちらは昼食と夕食(指定のカラム)のいずれかに、芋を含む1日を抽出するイメージですね。)
私が気になったのは値を評価する際の「正規化」です。
Before values are compared, they are normalized and case folded with NFKC normalization.
NFKCという手法で正規化してから評価してくれるそうです。
次のような例が掲載されていました。
SELECT '\u2168 day' AS a, 'IX' AS b, CONTAINS_SUBSTR('\u2168', 'IX') AS result; +----------------------+ | a | b | result | +----------------------+ | Ⅸ day | IX | true | +----------------------+
\u2168
というのは Ⅸ
の記号のことなので、以下のように書いても同じ結果(true)です。
SELECT 'Ⅸ day' AS a, 'IX' AS b, CONTAINS_SUBSTR('Ⅸ day', 'IX') AS result;
日本語を扱っていると、全半角のカタカナの統一など、めんどうに感じることがあります。 その辺にどのくらい対応しているのか次章で試してみました。
全角/半角カタカナや記号の正規化を試してみる
さっそく試してみた結果です。(結果は執筆時点のもの。)
※ハイライトしている行がtrueを返したものです。
SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result; -- 例そのまま:true SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result; -- 全角blue:true SELECT CONTAINS_SUBSTR('the blue house', 'Blue house') AS result; -- 全角blue+全角スペース+全角house:true SELECT CONTAINS_SUBSTR('the bluё house', 'Blue house') AS result; -- ロシア語のe:false SELECT CONTAINS_SUBSTR('the bluЁ house', 'Bluё house') AS result; -- ロシア語のeとE:true
全半角アルファベットや全半角スペースはうまく正規化してくれました。
まあ、確かに「e」とロシア語の「ё」は別ものですよね。
SELECT CONTAINS_SUBSTR('全角ハンバーグ丼', 'ハンバーグ') AS result; -- そのまま:true SELECT CONTAINS_SUBSTR('半角ハンバーグ丼', 'ハンバーグ') AS result; -- 半角カタカナ:true SELECT CONTAINS_SUBSTR('半角ハンハーク丼', 'ハンバーグ') AS result; -- 半角カタカナ濁点無し:false SELECT CONTAINS_SUBSTR('ひらがなはんばーぐ丼', 'ハンバーグ') AS result; -- ひらがな:false SELECT CONTAINS_SUBSTR('チルダハンバ~グ丼', 'ハンバーグ') AS result; -- 全角チルダ:false SELECT CONTAINS_SUBSTR('チルダハンバ~グ丼', 'ハンバ~グ') AS result; -- 半角と全角のチルダ:true SELECT CONTAINS_SUBSTR('ハイフン記号ハンバ―グ丼', 'ハンバーグ') AS result; -- 記号のハイフン:false SELECT CONTAINS_SUBSTR('㊤ハンバーグ丼', '上ハンバーグ') AS result; -- ㊤と上:true SELECT CONTAINS_SUBSTR('↑ハンバーグ丼', '上ハンバーグ') AS result; -- ↑と上:false SELECT CONTAINS_SUBSTR('㊕ハンバーグ丼', '特ハンバーグ') AS result; -- ㊕と特:true SELECT CONTAINS_SUBSTR('(おすすめ)ハンバーグ丼', '(おすすめ)ハンバーグ') AS result; -- 全角と半角の括弧:true
カタカナの全角/半角はうまく評価しれくれるみたいですね。
記号系が予想外。すごい…
↓そして、日本の記号でも特に愛されてやまない株式会社。
SELECT CONTAINS_SUBSTR('㊑ハンバーグ丼', '株ハンバーグ') AS result; -- まるカブ:true SELECT CONTAINS_SUBSTR('㈱ハンバーグ丼', '(株)ハンバーグ') AS result; -- 括弧カブ(全角検索):true SELECT CONTAINS_SUBSTR('㈱ハンバーグ丼', '(株)ハンバーグ') AS result; -- 括弧カブ(半角検索):true SELECT CONTAINS_SUBSTR('㍿ハンバーグ丼', '株式会社ハンバーグ') AS result; -- 4文字を1文字で:true
↑に記載のパターンは全てtrue評価!
ちなみに似たような NORMALIZE_AND_CASEFOLD
という関数は前からあったみたいですね…
( 公式ドキュメント )
おわりに
便利そうな関数の紹介でした。